﻿use master
go
drop database base
go
create database base
go
use base
go
----------------------------------------------------
--created_date: 13/08/2012
--desc: tbl_users
create table tbl_users
(
	user_id int identity(1,1) primary key,
	email varchar(150),
	username varchar(50),
	password varchar(150),
	user_status tinyint,
	created_date datetime,
	active_date datetime,
	activation_code varchar(10),
	salt varchar(32)
)
go
----------------------------------------------------
--created_date:13/08/2012
--desc:table user information
create table tbl_user_infor
(
	user_infor_id int identity(1,1) primary key,
	user_id int references tbl_users(user_id),
	infor_key nvarchar(30),
	infor_value nvarchar(500)
)
go
----------------------------------------------------
--created_date:13/08/2012
--desc:group user
--eg: Project Mgr,Consultant,Administrator,...
create table tbl_user_group
(
	group_id int identity(1,1) primary key,
	group_name nvarchar(30),
	group_desc nvarchar(150),
	group_enable bit,
	group_parent_id int references tbl_user_group(group_id) 	
)
go
-----------------------------------------------------
--created_date:13/08/2012
--desc:action of user
--eg: Editor, reviewer,delete,...
create table tbl_ref_role_action
(
	role_action_id int identity(1,1) primary key,
	role_action_name nvarchar(30),
	role_action_desc nvarchar(150)
)
go
---------------------------------------------------
--created_date:13/08/2012
--desc:references role_action , group , user_id
create table tbl_user_ref_roles
(
	user_role_id int identity(1,1) primary key,
	group_id int references tbl_user_group(group_id),
	--role_action_id int references tbl_ref_role_action(role_action_id),		//cách 1
	user_id int references tbl_users(user_id),
	date_from datetime,
	date_to datetime
)
go
--------------------------------------------------
--created_date:13/08/2012
--desc:table page in system, page_status = 1 is enable, page_status=0 is disable, page_type=1 is admin page
create table tbl_page
(
	page_id int identity(1,1) primary key,
	page_name nvarchar(100),
	page_desc nvarchar(150),
	page_path varchar(150),
	page_status tinyint,
	page_type tinyint,
	page_order int,
	page_parent int references tbl_page(page_id)
)
go
--------------------------------------------------
--created_date:13/08/2012
--desc: assign role page
create table tbl_page_role
(
	page_role_id int identity(1,1) primary key,
	page_id int references tbl_page(page_id),
	group_id int references tbl_user_group(group_id),
	role_action_id int references tbl_ref_role_action(role_action_id),    --cách 2
	created_date datetime
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc:table language in system
create table tbl_language
(
	language_id int identity(1,1) primary key,
	language_code varchar(4),
	language_flag varchar(150),
	language_desc nvarchar(50),
	language_status tinyint
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc:table product category
create table tbl_product_category
(
	category_id int identity(1,1) primary key,
	images varchar(100),
	category_status tinyint,
	category_sort int,
	category_param varchar(50),
	category_parent int references tbl_product_category(category_id)
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc: table product category item
create table tbl_product_category_item
(
	category_item_id int identity primary key,
	category_name nvarchar(50),
	category_alias varchar(50),
	category_desc ntext,
	category_desc_short nvarchar(150),
	category_title nvarchar(150),
	category_meta_keyword nvarchar(150),
	category_meta_description nvarchar(150),
	category_item_enable bit,
	language_id int references tbl_language(language_id),
	category_id int references tbl_product_category(category_id)
)
go

---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc: table product
create table tbl_product
(
	product_id int identity(1,1) primary key,
	product_code varchar(10),
	product_status tinyint,
	created_date datetime,
	modify_date datetime,
	modify_by int,
	quantity int,
	price float,
	old_price float,
	images nvarchar(100),
	tax int,
	sales int
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:06/05/2013
--desc: table related product
create table tbl_product_relate
(
	relate_id int identity(1,1) primary key,
	product_id_a int,
	product_id_b int 
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc: table product item
create table tbl_product_item
(
	product_item_id int identity(1,1) primary key,
	product_name nvarchar(100),
	product_alias varchar(100),
	product_desc_short nvarchar(150),
	product_desc nvarchar(max),
	product_features ntext,
	product_title nvarchar(150),
	product_meta_keyword nvarchar(150),
	product_meta_description nvarchar(150),
	product_item_enable bit,
	product_id int references tbl_product(product_id),
	language_id int references tbl_language(language_id)
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc: table product images
create table tbl_product_images
(
	images_id int identity(1,1) primary key,
	images varchar(150),
	images_thumb varchar(150),
	images_code varchar(10),
	images_name varchar(30),
	product_id int references tbl_product(product_id),
	images_enable bit
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc: table product ref category
create table tbl_product_ref_category
(
	product_ref_category_id int identity(1,1) primary key,
	category_id int references tbl_product_category(category_id),
	product_id int references tbl_product(product_id)
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc: table article category
create table tbl_article_category
(
	category_id int identity(1,1) primary key,
	images varchar(100),
	category_status tinyint,
	category_sort int,
	category_param varchar(50),
	category_parent int references tbl_article_category(category_id)
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc: table article category item
create table tbl_article_category_item
(
	category_item_id int identity primary key,
	category_name nvarchar(50),
	category_alias varchar(50),
	category_desc ntext,
	category_desc_short nvarchar(150),
	category_title nvarchar(150),
	category_meta_keyword nvarchar(150),
	category_meta_description nvarchar(150),
	category_item_enable bit,
	language_id int references tbl_language(language_id),
	category_id int references tbl_article_category(category_id)
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc: table article 
create table tbl_article
(
	article_id int identity(1,1) primary key,
	images varchar(100),
	article_status tinyint,
	created datetime,
	modifyed datetime,
	created_by int,
	modifyed_by int,
	url varchar(150)
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc: table article item
create table tbl_article_item
(
	article_item_id int identity(1,1) primary key,
	title nvarchar(200),
	alias varchar(200),
	intro_text nvarchar(250),
	full_text ntext,
	meta_key nvarchar(200),
	meta_desc nvarchar(200),
	tag nvarchar(150),
	related varchar(150),
	article_item_enable bit,
	article_id int references tbl_article(article_id),
	language_id int references tbl_language(language_id)	
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:17/08/2012
--desc: table article ref category
create table tbl_article_ref_category
(
	article_ref_category_id int identity(1,1) primary key,
	category_id int references tbl_article_category(category_id),
	article_id int references tbl_article(article_id)
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:30/03/2013
--desc: table infor customer (after register)
create table tbl_customer
(
	customer_id int identity(1,1) primary key,
	full_name nvarchar(50),
	address nvarchar(50),
	phone varchar(20),
	mobile varchar(20),
	email varchar(50),
	district nvarchar(40),
	prencint nvarchar(40),
	city nvarchar(20),
	status bit,
	created_date datetime,
	user_id int references tbl_users(user_id)
)
---------------------------------------------------
--created:Son nguyen van
--created_date:30/03/2013
--desc: table cart
create table tbl_cart
(
	record_id int identity(1,1) primary key,
	cart_id varchar(50),
	product_id int references tbl_product(product_id),
	count int,
	date_created datetime
)
go
----------------------------------------------------
--created:Son nguyen van
--created_date:30/03/2013
--desc: table order
create table tbl_order
(
	order_id int identity(1,1) primary key,
	order_date datetime,
	customer_id int references tbl_customer(customer_id),
	full_name nvarchar(50),
	address nvarchar(50),
	phone varchar(20),
	mobile varchar(20),
	email varchar(50),
	district nvarchar(40),
	prencint nvarchar(40),
	city nvarchar(20),
	total float,
	created_date datetime
)
go
----------------------------------------------------
--created:Son nguyen van
--created_date:30/03/2013
--desc: table order detail
create table tbl_order_detail
(
	detail_id int identity(1,1) primary key,
	order_id int references tbl_order(order_id),
	product_id int references tbl_product(product_id),
	quantity int,
	price_value float
)
go
---------------------------------------------------
--created:Son nguyen van
--created_date:22/06/2013
--desc: table related product
create table tbl_article_relate
(
	relate_id int identity(1,1) primary key,
	article_id_a int,
	article_id_b int 
)
go

---------------------------------------------------
--created:Son nguyen van
--created_date:23/06/2013
--desc: table email content
create table tbl_email_template
(
	email_id int identity(1,1) primary key,
	email_name nvarchar(50),
	email_subject nvarchar(250),
	email_content ntext,
	email_order int,
	modified datetime,
	email_status tinyint,
	modified_by int 
)
go
